package com.ddproject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class DbUtity {
	public Connection con = null;  
	String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=ddproject;user=sa;password=sa";//sa身份连接  

	
	public List obtainListByName(String namelist){
		
        // Declare the JDBC objects.  
        System.out.println(namelist);
        Statement stmt = null;  
        ResultSet rs = null;  
        List<Object> list = new ArrayList<Object>();
        JSONObject json ;
        try {  
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = DriverManager.getConnection(url); 
			String[] namearray = namelist.split(",");
			String wherestr ="";
			wherestr+="XZHQM LIKE '%"+namearray[0]+"%'";
			if(namearray.length>1){
				
				for(int i=1,length=namearray.length;i<length;i++){
					
					wherestr+=" or XZHQM LIKE '%"+namearray[i]+"%'";
				}
			}
			System.out.println(wherestr);
            String SQL = "SELECT  * FROM cgml where "+wherestr;  
            stmt = con.createStatement();  
            rs = stmt.executeQuery(SQL);   
            while (rs.next()) {  
            	json = new JSONObject();
            	json.put("dabh", rs.getObject("PKIIB"));
            	json.put("damc", rs.getObject("PKIIA"));
            	json.put("type", rs.getObject("ZLLBMC"));
            	json.put("DAGMC", rs.getObject("DAGMC"));
            	list.add(json);
                System.out.println(rs.getString(4) + " " + rs.getString(17));  
            }  
        }  
  
        // Handle any errors that may have occurred.  
        catch (Exception e) {  
            e.printStackTrace();  
        }  
  
        finally {  
            if (rs != null)  
                try {  
                    rs.close();  
                } catch (Exception e) {  
                }  
            if (stmt != null)  
                try {  
                    stmt.close();  
                } catch (Exception e) {  
                }  
            if (con != null)  
                try {  
                    con.close();  
                } catch (Exception e) {  
                }  
        }
        
        return list;
	}
	
	
public List checkByName(String namelist){ 
        System.out.println(namelist);
        Statement stmt = null;  
        ResultSet rs = null;  
        List<Object> list = new ArrayList<Object>();
        JSONObject json ;
        try {  
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = DriverManager.getConnection(url); 
			String wherestr ="";
			wherestr+="PKIIA LIKE '%"+namelist+"%'";
			System.out.println(wherestr);
            String SQL = "SELECT  * FROM cgml where "+wherestr;  
            stmt = con.createStatement();  
            rs = stmt.executeQuery(SQL);   
            while (rs.next()) {  
            	json = new JSONObject();
            	json.put("dabh", rs.getObject("PKIIB"));
            	json.put("damc", rs.getObject("PKIIA"));
            	json.put("type", rs.getObject("ZLLBMC"));
            	json.put("DAGMC", rs.getObject("DAGMC"));
            	list.add(json);
                System.out.println(rs.getString(4) + " " + rs.getString(17));  
            }  
        }  
  
        // Handle any errors that may have occurred.  
        catch (Exception e) {  
            e.printStackTrace();  
        }  
  
        finally {  
            if (rs != null)  
                try {  
                    rs.close();  
                } catch (Exception e) {  
                }  
            if (stmt != null)  
                try {  
                    stmt.close();  
                } catch (Exception e) {  
                }  
            if (con != null)  
                try {  
                    con.close();  
                } catch (Exception e) {  
                }  
        }
        
        return list;
	}
	
	
	public static void main(String[] args) {
		DbUtity db = new DbUtity();
		db.obtainListByName("");
	}
}
